﻿using System;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Text;
using SdmxMl.Manager;
using SdmxMl.Common;
using SdmxMl.Structure;

namespace SdmxMl.Fact_Dim
{
    public class OraFactDim
    {
        private RefNomen refVarHelper;
        private TextFormatType tfName;
        private TextFormatType tfLang;
        private TextFormatType tfFlag;
        private TextFormatType tfId;

        public OraFactDim(RefNomen refN)
        {
            refVarHelper = refN;

            tfName = new TextFormatType();
            tfName.TextType = TextTypeType.String;
            tfName.maxLength = "2000";

            tfLang = new TextFormatType();
            tfLang.TextType = TextTypeType.String;
            tfLang.maxLength ="2";

            tfFlag = new TextFormatType();
            tfFlag.TextType = TextTypeType.String;
            tfFlag.maxLength = "16";

            tfId = new TextFormatType();
            tfId.TextType = TextTypeType.String;
            tfId.maxLength = "255";
        }

        public string CreateCodelistDrop(CodeListType clt)
        {
            StringBuilder sbf = new StringBuilder(256);
            sbf.Append("DROP TABLE ").Append(NormalizeCodelistTableName(clt));
            sbf.Append(';').Append(Environment.NewLine);

            return sbf.ToString();
        }

        public string CreateHierarchyDrop(HierarchicalCodelistType hcl)
        {
            StringBuilder sbf = new StringBuilder(256);
            sbf.Append("DROP TABLE ").Append(NormalizeNomenTableName(hcl));
            sbf.Append(';').Append(Environment.NewLine);

            return sbf.ToString();
        }

        public string CreateDrop(KeyFamilyType dsd, Dictionary<string, NomenWithQuality> dicoDimCodes)
        {
            StringBuilder sbf = new StringBuilder(256);
            sbf.Append("DROP TABLE ").Append(NormalizeDsdTableName(dsd));
            sbf.Append(';').Append(Environment.NewLine);

            sbf.Append("DROP TABLE ").Append(NormalizeFlagTableName(dsd));
            sbf.Append(';').Append(Environment.NewLine);

            sbf.Append("DROP TABLE ").Append(NormalizeLinkTableName(dsd));
            sbf.Append(';').Append(Environment.NewLine);
            
            foreach (NomenWithQuality nmq in dicoDimCodes.Values)
            {
                if (nmq.IsEmpty == false && nmq.IsHierarchicalCodelist == false)
                {
                    sbf.Append("DROP TABLE ").Append(NormalizeNomenTableName(nmq));
                    sbf.Append(';').Append(Environment.NewLine);
                }
            }

            return sbf.ToString();
        }

        public string CreateFact(KeyFamilyType dsd, List<BaseDimensionType> lbd)
        {
            StringBuilder sbf = new StringBuilder(256);

            sbf.Append("CREATE TABLE ").Append(NormalizeDsdTableName(dsd)).Append(" (");

            foreach (BaseDimensionType bdt in lbd)
            {
                TextFormatType tf = refVarHelper.GetTextFormatTypeOfBaseDim(bdt);
                AppendCol(bdt.ConceptInfo.Id, tf, sbf);
            }

            string command = sbf.ToString().TrimEnd(',');
            command += ");";
            command += Environment.NewLine;

            return command;
        }

        public string CreateDimCode(NomenWithQuality nq)
        {
            StringBuilder sbf = new StringBuilder(256);

            sbf.Append("CREATE TABLE ").Append(NormalizeNomenTableName(nq)).Append(" (");
            TextFormatType tf = refVarHelper.GetTextFormatTypeOfBaseDim(nq.Bdt);
            AppendCol("CODE", tf, sbf);
            AppendCol("NAME", tfName, sbf);
            AppendCol("LANG", tfLang, sbf);


            string command = sbf.ToString().TrimEnd(',');
            command += ");";
            command += Environment.NewLine;

            sbf = new StringBuilder(command);

            // Now fill
            if (nq.Clt != null)
                foreach (CodeType c in nq.Clt.CodeList)
                {
                    foreach (string lg in LangSupport.SupportedLanguages)
                    {
                        sbf.Append(" INSERT INTO ").Append(NormalizeNomenTableName(nq)).Append("(CODE,NAME,LANG) VALUES (");
                        AppendValue(tf, c.Id, sbf, ",");
                        AppendValue(tfName, c.Name.GetForLanguage(lg), sbf, ",");
                        AppendValue(tfLang, lg.ToUpper(), sbf, ");" + Environment.NewLine);
                    }
                }

            return sbf.ToString();
        }

        public string CreateDimCode(CodeListType clt, TextFormatType tf)
        {
            if (tf == null)
                tf = tfFlag;

            StringBuilder sbf = new StringBuilder(256);

            sbf.Append("CREATE TABLE ").Append(NormalizeCodelistTableName(clt)).Append(" (");
            AppendCol("CODE", tf, sbf);
            AppendCol("NAME", tfName, sbf);
            AppendCol("LANG", tfLang, sbf);


            string command = sbf.ToString().TrimEnd(',');
            command += ");";
            command += Environment.NewLine;

            sbf = new StringBuilder(command);

            // Now fill
            if (clt != null)
                foreach (CodeType c in clt.CodeList)
                {
                    foreach (string lg in LangSupport.SupportedLanguages)
                    {
                        sbf.Append(" INSERT INTO ").Append(NormalizeCodelistTableName(clt)).Append("(CODE,NAME,LANG) VALUES (");
                        AppendValue(tf, c.Id, sbf, ",");
                        AppendValue(tfName, c.Name.GetForLanguage(lg), sbf, ",");
                        AppendValue(tfLang, lg.ToUpper(), sbf, ");" + Environment.NewLine);
                    }
                }

            return sbf.ToString();
        }

        public string CreateDimHierarchyCode( Dictionary<string, CodeListType> dico, TreeNode rootNode, HierarchicalCodelistType hcl, int finalLevel)
        {
            
            StringBuilder sbf = new StringBuilder(256);
            string tName = NormalizeNomenTableName(hcl);
            sbf.Append("CREATE TABLE ").Append(tName).Append(" (");
            TextFormatType tf = tfFlag;

            for (int i = finalLevel; i > 0; --i)
            {
                AppendCol("CODE_L" + i.ToString(), tf, sbf);
                AppendCol("NAME_L" + i.ToString(), tfName, sbf);
            }
            AppendCol("LANG", tfLang, sbf);

            string command = sbf.ToString().TrimEnd(',');
            command += ");";
            command += Environment.NewLine;

            sbf = new StringBuilder(command);

            // Now fill
            foreach (TreeNode node in rootNode.Nodes)
            {
                FillHierarchyCode(dico, tName, sbf, node, finalLevel, 1);
            }

            return sbf.ToString();
        }
        
        private void FillHierarchyCode( Dictionary<string, CodeListType> dico, string tName,  StringBuilder sbf, TreeNode node, int finalLevel, int level)
        {
             CodeRefType crt = node.Tag as CodeRefType;
            TreeNode levelNode = node;

            if (level == finalLevel)
            {
                foreach (string lg in LangSupport.SupportedLanguages)
                {
                    sbf.Append(" INSERT INTO ").Append(tName).Append("(");
                    for (int i = finalLevel; i > 0; --i)
                    {
                        sbf.Append("CODE_L").Append(i).Append(',');
                        sbf.Append("NAME_L").Append(i).Append(',');
                    }
                    sbf.Append("LANG) VALUES (");


                    node = levelNode;
                    for (int i = finalLevel; i > 0; --i)
                    {
                        crt = node.Tag as CodeRefType;
                        AppendValue(tfFlag, crt.CodeID, sbf, ",");

                        CodeListType clType = dico[crt.CodelistAliasRef];
                        ISdmxHierarchyListItem item = clType.CodeList.LocateItem(crt.CodeID);

                        if (item == null)
                            throw new ApplicationException("Unable to proceed. Hierarchy referred code Id '" +
                                crt.CodeID + "' is not present in codelist: " + clType.FullIdent);

                        AppendValue(tfName, item.Name.GetForLanguage(lg), sbf, ",");
                        node = node.Parent;
                    }

                    AppendValue(tfLang, lg.ToUpper(), sbf, ");" + Environment.NewLine);
                }
            }
            else if (level < finalLevel)
            {
                foreach (TreeNode n in levelNode.Nodes)
                    FillHierarchyCode(dico, tName, sbf, n, finalLevel, level + 1);
            }
        }

         public string CreateDimHierarchyCode(NomenWithQuality nq)
        {
            StringBuilder sbf = new StringBuilder(256);


            return sbf.ToString();
        }

        protected void AppendValue(TextFormatType tf, string val, StringBuilder sbf, string termin)
        {
            if (tf.IsNumeric)
                sbf.Append(val);
            else
                sbf.Append("'").Append(standardiseText(val)).Append("'");
            if (string.IsNullOrEmpty(termin) == false)
                sbf.Append(termin);
       }

        protected string standardiseText(string text)
        {
            return text.Replace("'", "''");
        }

        private void AppendCol(string colName, TextFormatType tf, StringBuilder sbf)
        {
            sbf.Append(colName).Append(" ");
            string maxi = string.IsNullOrEmpty(tf.maxLength) ? "255" : tf.maxLength;

            if (tf.IsNumeric)
            {
                maxi = string.IsNullOrEmpty(tf.maxLength) ? "15" : tf.maxLength;
                sbf.Append("NUMBER(").Append(maxi).Append(",");
                if (string.IsNullOrEmpty(tf.decimals))
                    sbf.Append(0);
                else
                    sbf.Append(tf.decimals);
                sbf.Append("),");
            }
            else
            {
                sbf.Append(" VARCHAR2(");
                sbf.Append(maxi).Append(" BYTE),");

            }
        }

        private string NormalizeDsdTableName(KeyFamilyType dsd)
        {
            string t = dsd.Id;
            if (t.StartsWith("DSD_"))
                t = dsd.Id.Substring(4);
            t = "XD_" + t;
            if (dsd.Version != "1.0")
            {
                string vers = dsd.Version.Replace('.', '_');
                t += "_" + vers;
            }
            return t.Length > 30 ? t.Substring(0, 30) : t;
        }

        private string NormalizeFlagTableName(KeyFamilyType dsd)
        {
            string t = dsd.Id;
            if (t.StartsWith("DSD_"))
                t = dsd.Id.Substring(4);
            t = "XD_FLG_" + t;
            if (dsd.Version != "1.0")
            {
                string vers = dsd.Version.Replace('.', '_');
                t += "_" + vers;
            }
            return t.Length > 30 ? t.Substring(0, 30) : t;
        }

        private string NormalizeLinkTableName(KeyFamilyType dsd)
        {
            string t = dsd.Id;
            if (t.StartsWith("DSD_"))
                t = dsd.Id.Substring(4);
            t = "XD_LNK_" + t;
            if (dsd.Version != "1.0")
            {
                string vers = dsd.Version.Replace('.', '_');
                t += "_" + vers;
            }
            return t.Length > 30 ? t.Substring(0, 30) : t;
        }

        private string NormalizeNomenTableName(HierarchicalCodelistType hclt)
        {
            string t = "invalid";
            if (hclt != null)
            {
                t = hclt.Id;
                if (t.StartsWith("HCL_"))
                    t = hclt.Id.Substring(4);
                t = "XI_" + t;
                if (hclt.Version != "1.0")
                {
                    string vers = hclt.Version.Replace('.', '_');
                    t += "_" + vers;
                }
            }
            return t;
        }
        private string NormalizeCodelistTableName(CodeListType clt)
        {
            string t = "invalid";
            if (clt != null)
            {
                t = clt.Id;
                if (t.StartsWith("CL_"))
                    t = clt.Id.Substring(3);
                t = "XI_" + t;
                if (clt.Version != "1.0")
                {
                    string vers = clt.Version.Replace('.', '_');
                    t += "_" + vers;
                }
            }

            return t;
        }
        private string NormalizeNomenTableName(NomenWithQuality nq)
        {
            string t = "invalid";
            if (nq.IsHierarchicalCodelist)
            {
                HierarchicalCodelistType hclt = nq.Hclt;
                if (hclt != null)
                {
                    t = NormalizeNomenTableName(hclt);
                }
            }
            else
            {
                CodeListType clt = nq.Clt == null ? nq.CltQuality : nq.Clt;
                if (clt != null)
                {
                    t = NormalizeCodelistTableName(clt);
                }
            }


            return t.Length > 30 ? t.Substring(0, 30) : t;
        }


        public string CreateFLagsInfo(KeyFamilyType dsd, ListVarFlags listFlags)
        {
            StringBuilder sbf = new StringBuilder(256);
            string tableName = NormalizeFlagTableName(dsd);
            sbf.Append("CREATE TABLE ").Append(tableName).Append(" (");
            AppendCol("CODE", tfFlag, sbf);
            AppendCol("CONCEPT", tfId, sbf);
        //    AppendCol("TYPE", tfId, sbf);


            string command = sbf.ToString().TrimEnd(',');
            command += ");";
            command += Environment.NewLine;

            sbf = new StringBuilder(command);

            // Now fill
            foreach (BaseDimensionType bdt in listFlags)
            {
                TextFormatType tf = refVarHelper.GetTextFormatTypeOfBaseDim(bdt);
                ConceptType c = refVarHelper.GetConcept(bdt.ConceptInfo.Id, bdt.ConceptSchemeInfo);
                if (c == null)
                    throw new ApplicationException("Unable to get concept: " + bdt.ConceptInfo.Id);

                // Only subset of codes to transmit ?
                List<string> listSubset = refVarHelper.GetQualityCodelistSubset(bdt);
                foreach (string s in listSubset)
                {
                    sbf.Append(" INSERT INTO ").Append(tableName).Append("(CODE,CONCEPT) VALUES (");
                    AppendValue(tfFlag, s.Trim(), sbf, ",");
                    AppendValue(tfId, bdt.ConceptInfo.Id.Substring(0, bdt.ConceptInfo.Id.Length - 2), sbf, ");" + Environment.NewLine);
                    //      AppendValue(tfFlag, tf.TextType.ToString(), sbf, ");" + Environment.NewLine);
                }
            }
                    

            return sbf.ToString();
        }

        public string CreateLinkInfo(KeyFamilyType dsd, List<BaseDimensionType> lbd, List<NomenWithQuality> listNomenConcepts)
        {
            StringBuilder sbf = new StringBuilder(256);
            string tableName = NormalizeLinkTableName(dsd);
            sbf.Append("CREATE TABLE ").Append(tableName).Append(" (");
            AppendCol("CONCEPT", tfId, sbf);
            AppendCol("NAME", tfName, sbf);
            AppendCol("DIM", tfId, sbf);
            AppendCol("LANG", tfLang, sbf);
            AppendCol("TYPE", tfId, sbf);


            string command = sbf.ToString().TrimEnd(',');
            command += ");";
            command += Environment.NewLine;

            sbf = new StringBuilder(command);

            // Now fill
            foreach (BaseDimensionType bdt in lbd)
            {
                TextFormatType tf = refVarHelper.GetTextFormatTypeOfBaseDim(bdt);
                string codelistLink = LinkCodelistForConcept(listNomenConcepts, bdt);

                ConceptType c = refVarHelper.GetConcept(bdt.ConceptInfo.Id, bdt.ConceptSchemeInfo);

                if (c == null)
                    throw new ApplicationException("Unable to get concept: " + bdt.ConceptInfo.Id);
                string aType = codelistLink.Length > 0 ? "Codelist" : tf.TextType.ToString();

                foreach (string lg in LangSupport.SupportedLanguages)
                {
                    sbf.Append(" INSERT INTO ").Append(tableName).Append("(CONCEPT,NAME,DIM,LANG,TYPE) VALUES (");
                    AppendValue(tfId, bdt.ConceptInfo.Id, sbf, ",");
                    AppendValue(tfName, c.Name.GetForLanguage(lg), sbf, ",");
                    AppendValue(tfId, codelistLink, sbf, ",");
                    AppendValue(tfLang, lg.ToUpper(), sbf, ",");
                    AppendValue(tfId, aType, sbf, ");" + Environment.NewLine);
                }
            }

            return sbf.ToString();
        }

        private string LinkCodelistForConcept(List<NomenWithQuality> listNomenConcepts, BaseDimensionType bdt)
        {
            string link = string.Empty;

            foreach (NomenWithQuality nm in listNomenConcepts)
                if (bdt == nm.Bdt && nm.Clt != null)
                {
                    link = NormalizeNomenTableName(nm);
                    break;
                }
            return link;
        }

    }


    
}
